﻿<%@ WebHandler Language="C#" Class="EmployeeWorkTimeCountReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 工时查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.6.9
 **/

public class EmployeeWorkTimeCountReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getUserInfo":
                        r = getUserInfo(jObject);//查询用户信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkerList":
                        r = getWorkerList(jObject);//查询工人列表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorktimeDetail":
                        r = getWorktimeDetail(jObject);//查询工时明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportWorktimeDetail":
                        exportWorktimeDetail(jObject);//员工工时明细导出
                        break;
                    case "getExportExcel":
                        getExportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询用户信息
    Result getUserInfo(JObject jObject)
    {
        string sql = @" 
SELECT e.employeeid,e.employeename,e.fullname,f.factoryname,f.factoryid,
       w.workcentername,w.workcenterid,t.teamname,t.teamid,er.rolenames,u.attributevalue factorycode
FROM employee e
LEFT JOIN Sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN teamemployee te ON te.employeeid = e.employeeid
LEFT JOIN team t ON t.teamid = te.teamid
LEFT JOIN 
( 
  SELECT e.employeeid,listagg(r.rolename,'、') WITHIN GROUP (ORDER BY r.rolename) rolenames   
  FROM employeerole er 
  LEFT JOIN employee e ON e.employeeid = er.employeeid  
  LEFT JOIN roledef r ON r.roleid = er.roleid  
  GROUP BY e.employeeid 
) er ON er.employeeid = e.employeeid
LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE e.employeeid = '{0}'";

        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult.Columns.Add("auth", typeof(string));

        foreach (DataRow dr in dtResult.Rows)
        {
            string rolenames = dr["rolenames"].ToString();
            if (rolenames.Contains("定额员") || rolenames.Contains("系统管理员") || rolenames.Contains("分厂领导") || rolenames.Contains("分厂管理者"))
            {
                dr["auth"] = "管理员";
                break;
            }

            if (dr["factoryname"].ToString() == "")
            {
                dr["auth"] = "管理员";
                break;
            }

            if (rolenames.Contains("工区长"))
            {
                if (dr["workcentername"].ToString() == "")
                {
                    dr["auth"] = "管理员";
                    break;
                }
                else
                {
                    dr["auth"] = "工区长";
                    break;
                }
            }

            if (rolenames.Contains("班组长"))
            {
                if (dr["teamname"].ToString() == "")
                {
                    if (dr["workcentername"].ToString() == "")
                    {
                        dr["auth"] = "管理员";
                        break;
                    }
                    else
                    {
                        dr["auth"] = "工区长";
                        break;
                    }
                }
                else
                {
                    dr["auth"] = "班组长";
                    break;
                }
            }

            if (rolenames.Contains("工人"))
            {
                dr["auth"] = "工人";
                break;
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询工人列表
    Result getWorkerList(JObject jObject)
    {
        string sql = @" 
SELECT distinct e.employeeid,e.employeename,e.fullname
FROM employee e
LEFT JOIN Sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN teamemployee te ON te.employeeid = e.employeeid
LEFT JOIN team t ON t.teamid = te.teamid
WHERE 1=1 ";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND sv.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND sv.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //班组
        if (jObject["teamid"] != null && jObject["teamid"].ToString() != "")
        {
            sql += string.Format(" AND te.teamid = '{0}'", jObject["teamid"].ToString());
        }

        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getWorkTimeSql(JObject jObject, bool isCurrentMonth = false)
    {
        string sql = @"
SELECT t.employeeid,t.fullname,t.employeename,ROUND(SUM(NVL(t.employeeWorktime,0)) / 60,2) worktime
FROM (
    SELECT NVL(wts.workerid,e.employeeid) employeeid,NVL(e2.fullname,e.fullname) fullname, NVL(e2.employeename,e.employeename) employeename,
           (CASE WHEN wts.containersteptimesheetid IS NOT NULL THEN NVL(wts.splitduration,0)  
                 WHEN wts.containersteptimesheetid IS NULL AND (wt.adjustdate IS NOT NULL OR wt.verificationdate IS NOT NULL) THEN NVL(wt.totalduration,0)
                 WHEN wts.containersteptimesheetid IS NULL AND wt.adjustdate IS NULL AND wt.verificationdate IS  NULL THEN ROUND((sd.durationperunit * (wt.qty-wt.scrapqty)) / NVL(wt.splitcount,1),2) END
           ) employeeWorktime
    FROM containersteptimesheet wt
    LEFT JOIN employee e ON e.employeeid = wt.workerid
    INNER JOIN containercurrentinfo cci ON cci.containerid = wt.containerid
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
    LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
    LEFT JOIN employee e2 ON e2.employeeid = wts.workerid
    WHERE wt.workerid IS NOT NULL";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND wt.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND wt.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //班组
        if (jObject["teamid"] != null && jObject["teamid"].ToString() != "")
        {
            sql += string.Format(" AND wt.teamid = '{0}'", jObject["teamid"].ToString());
        }

        //工人
        if (jObject["workerid"] != null && jObject["workerid"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e2.employeeid,e.employeeid) = '{0}'", jObject["workerid"].ToString());
        }
        
        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        if (isCurrentMonth)
        {
            //本月时间
            if (jObject["thismonth"] != null && jObject["thismonth"].ToString() != "")
            {
                JArray arr = JArray.Parse(jObject["thismonth"].ToString());
                if (arr.Count >= 2)
                {
                    sql += string.Format(" AND wt.checkdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
                    sql += string.Format(" AND wt.checkdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
                }
            }
        }
        else
        {
            //检验时间
            if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
            {
                JArray arr = JArray.Parse(jObject["checkdate"].ToString());
                if (arr.Count >= 2)
                {
                    sql += string.Format(" AND wt.checkdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
                    sql += string.Format(" AND wt.checkdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
                }
            }
        }
        
        //状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "未提交")
            {
                sql += " AND wt.submitdate IS NULL ";
            }
            if (status == "已提交")
            {
                sql += " AND wt.submitdate IS NOT NULL ";
            }
            if (status == "已核算")
            {
                sql += " AND wt.adjustdate IS NOT NULL ";
            }
            if (status == "已核销")
            {
                sql += " AND wt.verificationdate IS NOT NULL ";
            }
        }

        //工时提交时间
        if (jObject["tijiaodate"] != null && jObject["tijiaodate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["tijiaodate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND wt.submitdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND wt.submitdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        sql += @"
) t
WHERE t.employeeWorktime IS NOT NULL
GROUP BY t.employeeid,t.fullname,t.employeename
";
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getWorkTimeSql(jObject);
        DataTable dtWorkTime = dal.GetDataTable(sql);
        DataTable dtThisMonthWorkTime = new DataTable();
        if (jObject["thismonth"].ToString() == jObject["checkdate"].ToString())
        {
            //所选的检验时间和当月时间一样时，不必再查询本月工时
            dtThisMonthWorkTime = dtWorkTime;
        }
        else
        {
            dtThisMonthWorkTime = dal.GetDataTable(getWorkTimeSql(jObject, true));
        }

        dtWorkTime.Columns.Add("thismonthworktime",typeof(Decimal));//本月工时

        foreach (DataRow dr in dtWorkTime.Rows)
        {
            string filter = string.Format("employeeid = '{0}'",dr["employeeid"].ToString());
            DataRow[] drList = dtThisMonthWorkTime.Select(filter);
            if (drList.Length > 0)
            {
                dr["thismonthworktime"] = Convert.ToDouble(drList[0]["worktime"].ToString());
            }
            else
            {
                dr["thismonthworktime"] = 0;
            }
        }
        
        foreach (DataColumn col in dtWorkTime.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtWorkTime;
        r.total = Convert.ToInt32(dtWorkTime.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getWorktimeDetailSql(JObject jObject)
    {
        string sql = @"
SELECT NVL(e2.employeeid,e.employeeid) employeeid,NVL(e2.employeename,e.employeename) employeename,NVL(e2.fullname,e.fullname) fullname,
       cci.mfgordername,cci.productname,cci.familylistnames,cci.containername,wt.workflowstepname,wt.createdate reporttime,wt.checkdate,
       f.factoryname,w.workcentername,t.teamname,
       wt.submitdate sumitdate,
       NVL(wt.durationperunit,sd.durationperunit) durationperunit,
       wt.qty-wt.scrapqty qty,
       (CASE WHEN wts.containersteptimesheetid IS NOT NULL THEN wts.splitduration 
             WHEN wts.containersteptimesheetid IS NULL AND (wt.adjustdate IS NOT NULL OR wt.verificationdate IS NOT NULL) THEN wt.totalduration
             WHEN wts.containersteptimesheetid IS NULL AND wt.adjustdate IS NULL AND wt.verificationdate IS  NULL THEN ROUND((sd.durationperunit * (wt.qty-wt.scrapqty)) / NVL(wt.splitcount,1),2) END
        ) sumworktime,
       DECODE(wt.submitdate,NULL,'未提交','已提交') istijiao,
       DECODE(wt.adjustdate,NULL,'未核算','已核算') ishesuan,
       DECODE(wt.verificationdate,NULL,'未核销','已核销') ishexiao
FROM containersteptimesheet wt
INNER JOIN containercurrentinfo cci ON cci.containerid = wt.containerid
LEFT JOIN employee e ON e.employeeid = wt.workerid
LEFT JOIN factory f ON F.factoryid = wt.factoryid
LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid
LEFT JOIN Team t ON t.teamid = wt.teamid
LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
LEFT JOIN employee e2 ON e2.employeeid = wts.workerid
WHERE wt.workerid IS NOT NULL 
";
        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        //工序
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND wt.workflowstepname = '{0}'", jObject["workflowstepname"].ToString());
        }

        //检验时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND wt.checkdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND wt.checkdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND wt.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        //工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND wt.workcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //班组
        if (jObject["teamid"] != null && jObject["teamid"].ToString() != "")
        {
            sql += string.Format(" AND wt.teamid = '{0}'", jObject["teamid"].ToString());
        }

        //工人
        if (jObject["employeeid"] != null && jObject["employeeid"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e2.employeeid,e.employeeid) = '{0}'", jObject["employeeid"].ToString());
            sql += string.Format(" AND e.employeeid = '{0}'", jObject["employeeid"].ToString());
        }

        if (jObject["workerid"] != null && jObject["workerid"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e2.employeeid,e.employeeid) = '{0}'", jObject["workerid"].ToString());
        }

        //状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "未提交")
            {
                sql += " AND wt.submitdate IS NULL ";
            }
            if (status == "已提交")
            {
                sql += " AND wt.submitdate IS NOT NULL ";
            }
            if (status == "已核算")
            {
                sql += " AND wt.adjustdate IS NOT NULL ";
            }
            if (status == "已核销")
            {
                sql += " AND wt.verificationdate IS NOT NULL ";
            }
        }

        //工时提交时间
        if (jObject["tijiaodate"] != null && jObject["tijiaodate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["tijiaodate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND wt.submitdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND wt.submitdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }
        return sql;
    }

    //查询工时明细
    Result getWorktimeDetail(JObject jObject)
    {
        string sql = getWorktimeDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportWorktimeDetail(JObject jObject)
    {
        string sql = getWorktimeDetailSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("employeeid");
        dtResult.Columns.Remove("employeename");
        dtResult.Columns.Remove("fullname");
        dtResult.Columns.Remove("productname");
        dtResult.Columns.Remove("sumitdate");

        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["qty"].Caption = "合格数";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["reporttime"].Caption = "报工时间";
        dtResult.Columns["checkdate"].Caption = "检验时间";
        dtResult.Columns["durationperunit"].Caption = "单位工时(分)";
        dtResult.Columns["sumworktime"].Caption = "合计工时(分)";
        dtResult.Columns["istijiao"].Caption = "已提交";
        dtResult.Columns["ishexiao"].Caption = "已核销";
        dtResult.Columns["ishesuan"].Caption = "已核算";
        dtResult.Columns["familylistnames"].Caption = "机型";
        ExportHelper.ExportExcel(dtResult, "【" + jObject["fullname"].ToString() + "】工时明细");
    }

    void getExportExcel(JObject jObject)
    {
        string sql = getWorktimeDetailSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("employeeid");
        dtResult.Columns["employeename"].Caption = "员工号";
        dtResult.Columns["fullname"].Caption = "员工";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["sumitdate"].Caption = "工时提交时间";
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["qty"].Caption = "合格数";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["reporttime"].Caption = "报工时间";
        dtResult.Columns["checkdate"].Caption = "检验时间";
        dtResult.Columns["durationperunit"].Caption = "单位工时(分)";
        dtResult.Columns["sumworktime"].Caption = "合计工时(分)";
        dtResult.Columns["istijiao"].Caption = "已提交";
        dtResult.Columns["ishexiao"].Caption = "已核销";
        dtResult.Columns["ishesuan"].Caption = "已核算";
        dtResult.Columns["familylistnames"].Caption = "机型";
        ExportHelper.ExportExcel(dtResult, "工时查询报表");
    }
}